package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Research;
import com.ysd.util.DBManager;

public class ResearchDaoImpl {
	//查询所有设计师的内容
	//访问修饰符 返回值类型  方法名（参数列表） {方法体}
	 public List<Research> getResearchList(){
		List<Research> list=new ArrayList<Research>();
		Statement st=null;
    	ResultSet rs=null;
    	Connection conn=DBManager.getConnection();//获得连接
    	try {//正常情况下
			st=conn.createStatement();//创建statement对象
			String sql="select * from research";
			rs=st.executeQuery(sql);//执行sql语句
			while(rs.next()) {//遍历结果集
				int researchId=rs.getInt("researchId");
				String researchName=rs.getString("researchName");
				String statusName=rs.getString("statusName");
				String researchImg=rs.getString("researchImg");
				Research research=new Research(researchId,researchName,statusName,researchImg);
				list.add(research);
			}
			return list;
		} catch (SQLException e) {//异常
			e.printStackTrace();
			return null;
		}finally {//关闭连接
			DBManager.closeAll(conn, st, rs);
		}
		 
	 }
	 //分页查询
	 public List<Research> getResearchListPage(int page,int size){
			List<Research> list=new ArrayList<Research>();
			Statement st=null;
	    	ResultSet rs=null;
	    	Connection conn=DBManager.getConnection();//获得连接
	    	try {//正常情况下
				st=conn.createStatement();//创建statement对象
				String sql="SELECT * FROM research LIMIT "+(page-1)*size+","+size+"";
				rs=st.executeQuery(sql);//执行sql语句
				while(rs.next()) {//遍历结果集
					int researchId=rs.getInt("researchId");
					String researchName=rs.getString("researchName");
					String statusName=rs.getString("statusName");
					String researchImg=rs.getString("researchImg");
					Research research=new Research(researchId,researchName,statusName,researchImg);
					list.add(research);
				}
				return list;
			} catch (SQLException e) {//异常
				e.printStackTrace();
				return null;
			}finally {//关闭连接
				DBManager.closeAll(conn, st, rs);
			}
			 
		 }
	 /****
	  * 根据id删除设计师
	  */
	 public int delResearch(int id) {
		 return DBManager.executeUpdate("DELETE FROM research WHERE researchId="+id);
	 }
	 /***
	  * 添加设计师
	  */
	 public int addResearch(Research r) {
		 return DBManager.executeUpdate("insert into research values(null,'"+r.getResearchName()+"','"+r.getStatusName()+"','"+r.getResearchImg()+"')");
	 }
	 /***
	  * 根据设计师名字查询
	  */
	 public List<Research> getResearchByName(String name){
		 List<Research> rList=new ArrayList<Research>();
		 Connection conn=DBManager.getConnection();//获得连接
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM research WHERE researchName='"+name+"'");
			while(rs.next()) {
				int researchId=rs.getInt("researchId");
				String researchName=rs.getString("researchName");
				String statusName=rs.getString("statusName");
				String researchImg=rs.getString("researchImg");
				Research r=new Research(researchId, researchName, statusName, researchImg);
				rList.add(r);
			}
			return rList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 
	 //通过id获取
	 public  Research getResearchById(int rId) {
		 Research r=null;
		 Connection conn=DBManager.getConnection();
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM research WHERE researchId="+rId);
			while(rs.next()) {
				int researchId=rs.getInt(1);
				String researchName=rs.getString(2);
				String statusName=rs.getString(3);
				String researchImg=rs.getString(4);
				r=new Research(researchId, researchName, statusName, researchImg);
			}
		    return r;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 /*
	  * 修改设计师内容
	  * localhost:8080/huaduoyoupin/adminResearch?reqName=updateResearchById
	  */
	 public int updateResearchById(Research r) {
		 return DBManager.executeUpdate("UPDATE research set researchName='"+r.getResearchName()+"',statusName='"+r.getStatusName()+"',researchImg='"+r.getResearchImg()+"' WHERE researchId="+r.getResearchId()+"");
	 }
	 //批量删除
	 public int delectProductByIds(String ids) {
		 String sql="DELETE FROM research WHERE researchId in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	}
}
